"""
Case Type   : DISTINCT
Case Name   : 导入数据t_Ustore_DQL_Distinct_PREPOSE1
Create At   : 2022-02-26
Owner       : wang_zhengyuan
Description :
    1、设置参数enable_copy_server_files参数值为on
    2、gsql连接数据库，创建表
    3、使用copy命令插入数据
    4、创建索引
    5、环境清理
Expect      :
    1、参数值修改成功
    2、gsql连接数据库，创建表成功
    3、数据插入成功
    4、索引创建成功
    5、清理成功
History     :
"""

import os
import unittest
import subprocess

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node


class Opengauss_Function_Ustore_DQL_Distinct_Case0005(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = CommonSH('PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_Ustore_DQL_Distinct_PREPOSE1'
        self.i_name_list = []
        for i in range(0, 5):
            self.i_name_list.append(f"i_Ustore_DQL_Distinct_PREPOSE1_{i}")
        self.copy_file1 = 'prepose_table_001'
        self.cur_path_1 = os.path.join(macro.SCRIPTS_PATH, self.copy_file1)
        self.target_path = macro.DB_INSTANCE_PATH
        self.tar_path_1 = os.path.join(macro.DB_INSTANCE_PATH, self.copy_file1)

    def test_main(self):
        step_txt = '----step1:修改参数值，expect: 参数值修改成功----'
        self.log.info(step_txt)
        self.log.info('---------------获取并打印参数初始值------------------')
        self.show_copy_pram = f'show enable_copy_server_files;'
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.pram_init = cmd_result.strip().splitlines()[-2].strip()

        self.log.info('---------------开始修改参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  "enable_copy_server_files=on")

        self.log.info('---------------检查参数是否修改成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value1 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual('on', self.cmd_result_value1, "参数修改失败")

        step_txt = '----step2:创建表，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.t_name1};\
                CREATE TABLE {self.t_name1}\
                (\
                  COL_SMALLINT    SMALLINT,\
                  COL_INT         INTEGER,\
                  COL_BIGINT      BIGINT,\
                  COL_NUMERIC_S    NUMERIC(18,6),\
                  COL_NUMERIC_1    NUMERIC(16),\
                  COL_NUMERIC_B    NUMERIC(20,12),\
                  COL_NUMERIC_N    NUMERIC (19),\
                  COL_NUMERIC_2    NUMERIC (19,6),\
                  COL_DOUBLE     DOUBLE PRECISION,\
                  COL_REAL         REAL,\
                  COL_CHAR1     CHAR(100),\
                  COL_CHAR     CHAR(37),\
                  COL_CHARN     CHAR(67),\
                  COL_VARCHAR_N    VARCHAR(37),\
                  COL_VARCHAR   VARCHAR(1000),\
                  COL_TEXT       varchar(1000),\
                  COL_DATE     DATE,\
                  C_TS_WITHOUT     varchar(1000),\
                  C_TS_WITH        varchar(1000)\
                )  WITH (STORAGE_TYPE=USTORE); "
        self.log.info(create_sql)
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step3:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(step_txt)
        text = '----step3.1: 获取当前主机的IP地址 expect: 成功----'
        self.log.info(text)
        cmd = "ifconfig -a|grep inet6 -a2|grep broadcast|awk '{print $2}'"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '----step3.2: 复制文件到数据库主机对应目录 expect: 成功----'
        self.log.info(text)
        if self.cur_ip == self.pri_sh.node.ssh_host:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path_1} {self.target_path};"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_sh.node, self.copy_file1,
                              self.target_path)

        text = '----step3.3: 查看数据库主机目标路径下文件 expect: 成功----'
        self.log.info(text)
        cmd = f"ls -l {self.target_path}| grep -E '{self.copy_file1}'"
        self.log.info(cmd)
        res = self.pri_sh.node.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file1}', res, '文件拷贝失败')

        text = '----step3.4:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.tar_path_1}' " \
            f"delimiters ',';"
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 2697', '执行失败:' + step_txt)

        step_txt = '----step4: 创建索引,expect: 索引创建成功----'
        self.log.info(step_txt)
        column_list = ['C_TS_WITHOUT', 'COL_INT', 'COL_NUMERIC_S',
                       'COL_VARCHAR', 'COL_TEXT']
        for a in range(0, 5):
            create_sql = f"drop index if exists {self.i_name_list[a]} " \
                f"CASCADE;create index {self.i_name_list[a]} " \
                f"on {self.t_name1}({column_list[a]}) ;"
            self.log.info(create_sql)
            create_result = self.pri_sh.execut_db_sql(create_sql)
            self.log.info(create_result)
            self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                          create_result,
                          f'创建{self.i_name_list[a]}失败：' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step5: 环境清理，expect:清理成功----'
        self.log.info(step_txt)
        drop_sql = f'drop table if exists {self.t_name1};'
        self.log.info(drop_sql)
        self.log.info('由于后续用例依赖暂不删表')

        rm_cmd = f"rm -rf {self.tar_path_1} "
        rm_res = self.pri_sh.node.sh(rm_cmd).result()
        self.log.info(rm_res)

        self.log.info('---------------开始还原参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                f"enable_copy_server_files={self.pram_init}")

        self.log.info('---------------检查参数是否还原成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value2 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.assertEqual(self.pram_init, self.cmd_result_value2, "参数还原失败")
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
